﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System .Diagnostics;

namespace t01
{
    /// <summary>
    /// user的数据访问类：增删改查
    /// </summary>
    class UserDao
    {
        //MySQL的连接串描述
        private static string connStr = "server=localhost;user=root;password=;database=test";
        //private static string connStr = "server=localhost;uid=root;pwd=;database=test";
        /// <summary>
        /// 登陆检查
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public static int LoginCheck(string username, string password)
        {
            //默认无效的用户名和密码
            int number = 0;
            //构建Mysql连接对象
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                //比较用户名和密码的比较SQL语句
                string sql = "SELECT COUNT(id) FROM `ub_users`WHERE`username=`" + username + "`AND``password`='" + password + "'";
                Debug.WriteLine("SQL语句：" + sql);
                //创建SQL命令
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //执行SQL命令
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    number = Convert.ToInt32(result);
                }


            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据库访问异常。。。");
                Debug.WriteLine("============================");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("============================");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return number;
        }
        public static List<User>LoadData()
        {
            List<User> userList = new List<User>();
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                //比较用户名和密码的比较SQL语句
                string sql = "SELECT*FORM ub_users";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["Nickname"]);
                    user.IsAdmin = Convert.ToInt16(rdr["isAdmin"]) == 1 ? true : false;
                }
                rdr.Close();

            }
            catch (Exception ex)
            {

                Debug.WriteLine(ex.ToString());

            }
            finally
            {


            }

        }
        /// <summary>
        /// 按条件获取用户列表
        /// </summary>
        /// <param name="condition">条件参数值</param>
        /// <returns>符合条件的用户集合</returns>
        public static List<User>LoadData(string condition)
        {
            List<User> userList = new List<User>();
            MySqlConnection conn = new MySqlConnection();
            (connStr);
            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM tb_users WHERE username LIKE @username OR nickname LIKE @nickname";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全参数
                cmd.Parameters.AddWithValue("@username", "'" + condition + "'");
                cmd.Parameters.AddWithValue("@password", "'" + condition + "'");
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.IsAdmin = Convert.ToInt16(rdr["is_admin"])==1 ? true : false;
                }
            }
            catch (Exception ex)
            {

                throw;
            }
            return null;
        }
    }
}
/// <summary>
/// 
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static long  Insert(User user)
{
    long insertId = 0;
    MySqlConnection conn = new MySqlConnection(connStr);
    try
    {
        conn.Open();
        string sql = "INSERT INTO tb_users " + "(username, password, nickname, is_admin )" + "VALUES " + "(@username, @password, @nickname, @id_admin )";
        MySqlConnection cmd = new MySqlConnection(sql, conn);
        //参数绑定
        cmd.parameters.AddWithValue("@username", user.Username);
        cmd.parameters.AddWithValue("@password", user.Password);
        cmd.parameters.AddWithValue("@nickname", user.Nickname);
        cmd.parameters.AddWithValue("@is_Admin", user.IsAdmin);
        cmd.ExecteNonQuery();
        insertId = cmd.LastInsertedId;

    }catch(Exception ex)
    {

    }
}
/// <summary>
/// 
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static long Update(User user)
{
    long insertId = 0;
    MySqlConnection conn = new MySqlConnection(connStr);
    try
    {
        conn.Open();
        string sql = "UPDATE tb_users SET"
            +"username=@username"
            +"password=@password"
            +"nickname=@nickname"
            +"is_admin=@is_admin"
            +"WHERE id=@id";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        //参数绑定
        cmd.Parameters.AddWithValue("@username", user.Username);
        cmd.Parameters.AddWithValue("@password", user.Password);
        cmd.Parameters.AddWithValue("@nickname", user.Nickname);
        cmd.Parameters.AddWithValue("@is_Admin", user.IsAdmin);
        cmd.Parameters.AddWithValue("@id", user.Id);
        cmd.ExecteNonQuery();
       
    }
    catch (Exception ex)
    {
        Debug.WriteLine("数据更新的")
        throw;
    }
}